#-*- encoding: utf-8 -*-

import os
import win32com.client
from datetime import datetime,date

def extract_trade_from_UBS_front_office(file_path):

	if os.path.isfile(file_path):
		file_name = os.path.split(file_path)[1]
		file_prefix = os.path.splitext(file_name)[0]
		file_suffix = os.path.splitext(file_name)[1]
		
		if file_prefix[:28] == 'Exec_NBIM PF EX EQ SP Finals' and  file_suffix[1:].lower() == 'xls':

			xApp = win32com.client.Dispatch("Excel.Application")
			xBook = xApp.Workbooks.Open(file_path)
			xSht = xBook.Worksheets('Executions')
			row = xSht.Range("B65536").End(win32com.client.constants.xlUp).Row + 1
			
			trd_date = xSht.Cells(3, 1).Value
			trade_dict = {} #(ticker,trade_type):(quantity, cost_price)
			for i in range(3,row):
				quantity = xSht.Cells(i, 7).Value
				if quantity > 0:
					ticker = xSht.Cells(i, 3).Value
					trade_type = 'BUY' if  xSht.Cells(i, 5).Value == 'B' else 'SELL'
					cost_price = xSht.Cells(i, 11).Value
				
				if trade_dict.has_key((ticker, trade_type)):
					pre_quantity = trade_dict[(ticker, trade_type)][0]
					pre_cost = trade_dict[(ticker, trade_type)][1]
					
					trade_dict[(ticker, trade_type)][0] += quantity	
					trade_dict[(ticker, trade_type)][1] = (pre_quantity*pre_cost + quantity*cost_price)/(pre_quantity + quantity)
				else:
					trade_dict[(ticker,trade_type)] = [quantity, cost_price]
			
			trade_rcd = []
			for k, v in trade_dict.iteritems():
				trade_rcd.append([trd_date, k[0], k[1], v[0], v[1], 'CNY'])
								
			xBook.Close()
			del xApp
			
			return sorted(trade_rcd, key=lambda trade_rcd:trade_rcd[1], reverse = True)
			
		else:
			raise RuntimeError() + 'File is not ubs trading file:' + file_name
	else:
		raise RuntimeError() + 'File does not exist:' + file_path
		

def extract_trade_from_UBS_back_office(file_path):

	if os.path.isfile(file_path):
		file_name = os.path.split(file_path)[1]
		file_prefix = os.path.splitext(file_name)[0]
		file_suffix = os.path.splitext(file_name)[1]
		
		if file_prefix[:37] == '000000007305_QFII_execution_inx level' and file_suffix[1:].lower() == 'xls':

			xApp = win32com.client.Dispatch("Excel.Application")
			xBook = xApp.Workbooks.Open(file_path)
			xSht = xBook.Worksheets('Sheet1')
			row = xSht.Range("B65536").End(win32com.client.constants.xlUp).Row + 1
			trade_date = datetime.strptime(str(int(xSht.Cells(3, 18).Value)),'%Y%m%d').date()
			settle_date = trade_date
			trade_dict = {} #(ticker,trade_type)
			# :(quantity, cost_price, local_price, commission, levy, Stamp, regu_fee, transfer_fee)
			for i in range(19,row):
				ticker = xSht.Cells(i, 4).Value
				trade_type = 'BUY' if xSht.Cells(i, 5).Value == 'Buy' else 'SELL'
				quantity = xSht.Cells(i, 6).Value
				cost_price = abs(xSht.Cells(i, 17).Value/quantity)
				local_price = abs(xSht.Cells(i, 8).Value/quantity)
				commission = xSht.Cells(i, 16).Value
				levy = xSht.Cells(i, 11).Value
				Stamp = xSht.Cells(i, 10).Value
				regu_fee = xSht.Cells(i, 12).Value
				transfer_fee = xSht.Cells(i, 13).Value
				
				if trade_dict.has_key((ticker, trade_type)):
					pre_quantity = trade_dict[(ticker, trade_type)][0]
					pre_cost = trade_dict[(ticker, trade_type)][1]
					pre_local_cost = trade_dict[(ticker, trade_type)][2]
					
					trade_dict[(ticker, trade_type)][0] += quantity	
					trade_dict[(ticker, trade_type)][1] = (pre_quantity*pre_cost + quantity*cost_price)/(pre_quantity + quantity)
					trade_dict[(ticker, trade_type)][2] = (pre_quantity*pre_local_cost + quantity*local_price)/(pre_quantity + quantity)
					trade_dict[(ticker, trade_type)][3] += commission
					trade_dict[(ticker, trade_type)][4] += levy
					trade_dict[(ticker, trade_type)][5] += Stamp
					trade_dict[(ticker, trade_type)][6] += regu_fee
					trade_dict[(ticker, trade_type)][7] += transfer_fee
				else:
					trade_dict[(ticker,trade_type)] = [quantity, cost_price, local_price, commission, levy, Stamp, regu_fee, transfer_fee]
							
			xBook.Close()
			del xApp
			
			trade_rcd = []
			for k, v in trade_dict.iteritems():
				trade_rcd.append([trade_date, k[0], k[1], v[0], v[1], 'CNY', 'STOCK', settle_date,
								v[2], None, None, v[3], v[4], v[5], v[6], v[7], 'UBS'])
			return sorted(trade_rcd, key=lambda trade_rcd:trade_rcd[1], reverse = True)
			
		else:
			raise RuntimeError() + 'File is not ubs confirmation file:' + file_name
	else:
		raise RuntimeError() + 'File does not exist:' + file_path